package com.neusoft.neuiotms.dcom.service.hn.impl;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.google.common.collect.Lists;
import com.neusoft.bizcore.auth.common.bean.UserBean;
import com.neusoft.bizcore.web.constant.ExceptionConstants;
import com.neusoft.bizcore.web.exception.UnitedException;
import com.neusoft.bizcore.web.support.Searchable;
import com.neusoft.bizcore.web.utils.EasyPoiStylerColorImpl;
import com.neusoft.neuiotms.dcom.bean.ProjectUserBean;
import com.neusoft.neuiotms.dcom.converter.hn.HnInspectionTaskConverter;
import com.neusoft.neuiotms.dcom.dto.InstitutionsDTO;
import com.neusoft.neuiotms.dcom.dto.hn.HnInspectionTaskAssetDTO;
import com.neusoft.neuiotms.dcom.dto.hn.HnInspectionTaskDTO;
import com.neusoft.neuiotms.dcom.model.enums.HnInspectionTaskStatus;
import com.neusoft.neuiotms.dcom.model.hn.HnInspectionTask;
import com.neusoft.neuiotms.dcom.repository.hn.HnInspectionTaskRepository;
import com.neusoft.neuiotms.dcom.service.InstitutionsService;
import com.neusoft.neuiotms.dcom.service.hn.HnInspectionTaskService;
import com.neusoft.neuiotms.dcom.util.PermUtils;
import com.neusoft.neuiotms.micro.dcam.DcamMicroService;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@Service
public class HnInspectionTaskServiceImpl implements HnInspectionTaskService {

    @Autowired
    private HnInspectionTaskRepository hnInspectionTaskRepository;
    @Autowired
    private HnInspectionTaskConverter hnInspectionTaskConverter;
    @Autowired
    private InstitutionsService institutionsService;
    @PersistenceContext
    private EntityManager entityManager;
    @Autowired
    private DcamMicroService dcamMicroService;

    @Override
    public List<HnInspectionTaskDTO> index(final Searchable searchable, final UserBean userBean) {
        final List<InstitutionsDTO> institutions = this.institutionsService.getPermInstitutions(userBean);
        final List<String> institutionCodeArray =
                institutions.stream().map(it -> it.getCode()).collect(Collectors.toList());
        searchable.put("institutionCodeArray", institutionCodeArray);
        final List<HnInspectionTask> model = this.hnInspectionTaskRepository.index(searchable);
        return this.hnInspectionTaskConverter.toListDTO(model);
    }

    @Override
    public Page<HnInspectionTaskDTO> search(final Pageable pageable, final Searchable searchable,
            final UserBean userBean) {
        final List<InstitutionsDTO> institutions = this.institutionsService.getPermInstitutions(userBean);
        final List<String> institutionCodeArray =
                institutions.stream().map(it -> it.getCode()).collect(Collectors.toList());
        searchable.put("institutionCodeArray", institutionCodeArray);
        final Page<HnInspectionTask> page = this.hnInspectionTaskRepository.search(pageable, searchable);
        return this.hnInspectionTaskConverter.toPageDTO(page);
    }

    @Override
    public HnInspectionTaskDTO update(final Long id, final HnInspectionTaskDTO dto, final UserBean user) {
        final HnInspectionTask model = this.hnInspectionTaskRepository.getOne(id);
        if (model == null) {
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        if (user == null) {
            throw new UnitedException("当前用户不能为空");
        }
        dto.setUserCode(user.getUsername());
        dto.setUserName(user.getName());
        dto.setStatus(HnInspectionTaskStatus.inspecting);

        final HnInspectionTaskDTO result = this.hnInspectionTaskConverter
                .toDTO(this.hnInspectionTaskRepository.save(this.hnInspectionTaskConverter.toModel(dto)));

        // 更新DCAM资产在线状态
        //        final List<Long> onlineAssetIdArray = result.getAssets().stream().filter(asset -> asset.isOnline())
        //                .map(HnInspectionTaskAssetDTO::getAssetId).collect(Collectors.toList());
        //        if (onlineAssetIdArray.size() > 0) {
        //            this.dcamMicroService.updateAssetRunningStatus(true, onlineAssetIdArray.stream().toArray(Long[]::new));
        //        }
        //        final List<Long> offlineAssetIdArray = result.getAssets().stream().filter(asset -> !asset.isOnline())
        //                .map(HnInspectionTaskAssetDTO::getAssetId).collect(Collectors.toList());
        //        if (offlineAssetIdArray.size() > 0) {
        //            this.dcamMicroService.updateAssetRunningStatus(false, offlineAssetIdArray.stream().toArray(Long[]::new));
        //        }
        return result;
    }

    @Override
    public HnInspectionTaskDTO details(final Long id) {
        final HnInspectionTask model = this.hnInspectionTaskRepository.getOne(id);
        if (model == null) {
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        return this.hnInspectionTaskConverter.toDTO(model);
    }

    @Override
    public void delete(final Long id) {
        final HnInspectionTask model = this.hnInspectionTaskRepository.getOne(id);
        if (model == null) {
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        this.hnInspectionTaskRepository.delete(model);
    }

    @Override
    public HnInspectionTaskDTO submit(final Long id, final HnInspectionTaskDTO dto, final UserBean user) {
        final HnInspectionTask model = this.hnInspectionTaskRepository.getOne(id);
        if (model == null) {
            throw new UnitedException(ExceptionConstants.ENTITY_NOT_FOUND, new Object[] {id });
        }
        if (user == null) {
            throw new UnitedException("当前用户不能为空");
        }
        dto.setUserCode(user.getUsername());
        dto.setUserName(user.getName());
        dto.setStatus(HnInspectionTaskStatus.finished);
        dto.setActualDateTime(new Date());

        final HnInspectionTaskDTO result = this.hnInspectionTaskConverter
                .toDTO(this.hnInspectionTaskRepository.save(this.hnInspectionTaskConverter.toModel(dto)));

        // 更新DCAM资产在线状态
        final List<Long> onlineAssetIdArray = result.getAssets().stream().filter(asset -> asset.isOnline())
                .map(HnInspectionTaskAssetDTO::getAssetId).collect(Collectors.toList());
        if (onlineAssetIdArray.size() > 0) {
            this.dcamMicroService.updateAssetRunningStatus(true, onlineAssetIdArray.stream().toArray(Long[]::new));
        }
        final List<Long> offlineAssetIdArray = result.getAssets().stream().filter(asset -> !asset.isOnline())
                .map(HnInspectionTaskAssetDTO::getAssetId).collect(Collectors.toList());
        if (offlineAssetIdArray.size() > 0) {
            this.dcamMicroService.updateAssetRunningStatus(false, offlineAssetIdArray.stream().toArray(Long[]::new));
        }

        return result;
    }

    @Override
    public int getTaskCountByStatus(final UserBean user, final List<HnInspectionTaskStatus> status) {

        if (user == null) {
            throw new UnitedException("当前用户不能为空");
        }

        final ProjectUserBean projectInfo = this.institutionsService.getMyInstitutions(user);

        int count = 0;
        if (PermUtils.isWorker(user)) {
            final List<String> institutions = Lists.newArrayList(projectInfo.getDefaultInstituteCode());
            count = this.hnInspectionTaskRepository.countByInstitutionCodeInAndStatusIn(institutions,
                    status);
        } else if (PermUtils.isWorkerGroupLeader(user)) {
            final List<String> institutionCodeArray =
                    projectInfo.getInstitutions().stream().map(it -> it.getCode()).collect(Collectors.toList());
            count = this.hnInspectionTaskRepository.countByInstitutionCodeInAndStatusIn(institutionCodeArray,
                    status);
        } else {
            count = this.hnInspectionTaskRepository.countByStatusIn(status);
        }

        return count;
    }

    @SuppressWarnings("unchecked")
    @Override
    public Page<Map<String, Object>> report(Pageable pageable, final Map<String, Object> params, UserBean user) {
        if (params.containsKey("institution_code")) {
            params.put("institution_code", "'" + (String) params.get("institution_code") + "'");
        }
        if (PermUtils.isWorker(user)) {
            params.put("user_name", user.getUsername());
        } else if (PermUtils.isWorkerGroupLeader(user)) {
            if (!params.containsKey("institution_code")) {
                final List<InstitutionsDTO> insts = this.institutionsService.getPermInstitutions(user);
                final List<String> instCodeArray =
                        insts.stream().map(it -> "'" + it.getCode() + "'").collect(Collectors.toList());
                final String institution_code = String.join(",", instCodeArray);
                params.put("institution_code", institution_code);
            }
        }

        final String countSql = this.buildTotalSql(params);
        HnInspectionTaskServiceImpl.log.info(countSql);
        final Query query1 = this.entityManager.createNativeQuery(countSql);
        query1.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result1 = query1.getResultList();
        int total = 0;
        if (result1.size() > 0) {
            total = Integer.parseInt(String.valueOf(result1.get(0).get("count")));
        }

        final String sql = this.buildQuerySql(pageable, params);
        HnInspectionTaskServiceImpl.log.info(sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();
        return new PageImpl<>(result, pageable, total);
    }

    @SuppressWarnings("unchecked")
    @Override
    public Workbook exportReport(final Map<String, Object> params, UserBean user) {
        if (params.containsKey("institution_code")) {
            params.put("institution_code", "'" + (String) params.get("institution_code") + "'");
        }
        if (PermUtils.isWorker(user)) {
            params.put("user_name", user.getUsername());
        } else if (PermUtils.isWorkerGroupLeader(user)) {
            if (!params.containsKey("institution_code")) {
                final List<InstitutionsDTO> insts = this.institutionsService.getPermInstitutions(user);
                final List<String> instCodeArray =
                        insts.stream().map(it -> "'" + it.getCode() + "'").collect(Collectors.toList());
                final String institution_code = String.join(",", instCodeArray);
                params.put("institution_code", institution_code);
            }
        }
        final String sql = this.buildExportSql(params);
        HnInspectionTaskServiceImpl.log.info(sql);
        final Query query = this.entityManager.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        final List<Map<String, Object>> result = query.getResultList();

        final List<InspectionReportVO> list = new ArrayList<>(result.size() == 0 ? 1 : result.size());
        result.stream().map(t -> {
            final InspectionReportVO vo = new InspectionReportVO();
            vo.setStart_date_time(t.get("start_date_time"));
            vo.setInstitution_name(t.get("institution_name"));
            vo.setUser_name(t.get("user_name"));
            vo.setStatus_name(t.get("status_name"));
            vo.setActual_date_time(t.get("actual_date_time"));
            vo.setResult_text(t.get("result_text"));
            vo.setAsset_total_count(t.get("asset_total_count"));
            vo.setOnline_count(t.get("online_count"));
            vo.setOffline_count(t.get("offline_count"));
            return vo;
        }).forEach(list::add);

        final ExportParams param =
                new ExportParams("巡检统计", "巡检统计", ExcelType.XSSF);
        param.setStyle(EasyPoiStylerColorImpl.class);
        return ExcelExportUtil.exportExcel(param, InspectionReportVO.class, list);
    }

    private String buildTotalSql(final Map<String, Object> params) {
        final String statement = " count(*) as count ";
        return this.buildSql(params, statement);
    }

    private String buildQuerySql(Pageable pageable, final Map<String, Object> params) {
        final String statement = " t0.id, "
                + " DATE(t0.start_date_time) AS start_date_time, "
                + " t0.institution_name, "
                + " t0.user_name, "
                + " CASE t0. STATUS "
                + "WHEN 'notStarted' THEN "
                + " '待处理' "
                + "WHEN 'inspecting' THEN "
                + " '进行中' "
                + "WHEN 'finished' THEN "
                + " '已完成' "
                + "END AS status_name, "
                + " DATE(t0.actual_date_time) AS actual_date_time, "
                + " CASE "
                + "WHEN t1.normal_item_count = t1.total_item_count THEN "
                + " '正常' "
                + "WHEN t1.normal_item_count < t1.total_item_count THEN "
                + " '异常' "
                + "END AS result_text, "
                + " t2.total_count AS asset_total_count, "
                + " t2.online_count, "
                + " t2.offline_count ";
        String noPageSql = this.buildSql(params, statement);
        noPageSql += " limit " + pageable.getPageSize() + " offset " + pageable.getOffset();

        return noPageSql;
    }

    private String buildExportSql(final Map<String, Object> params) {
        final String statement = " t0.id, "
                + " DATE(t0.start_date_time) AS start_date_time, "
                + " t0.institution_name, "
                + " t0.user_name, "
                + " CASE t0. STATUS "
                + "WHEN 'notStarted' THEN "
                + " '待处理' "
                + "WHEN 'inspecting' THEN "
                + " '进行中' "
                + "WHEN 'finished' THEN "
                + " '已完成' "
                + "END AS status_name, "
                + " DATE(t0.actual_date_time) AS actual_date_time, "
                + " CASE "
                + "WHEN t1.normal_item_count = t1.total_item_count THEN "
                + " '正常' "
                + "WHEN t1.normal_item_count < t1.total_item_count THEN "
                + " '异常' "
                + "END AS result_text, "
                + " t2.total_count AS asset_total_count, "
                + " t2.online_count, "
                + " t2.offline_count ";
        return this.buildSql(params, statement);
    }

    private String buildSql(final Map<String, Object> params, String statement) {
        String where = "where 1=1 ";
        if (params.containsKey("status")) {
            where += "and t0.status='" + (String) params.get("status") + "' ";
        }
        if (params.containsKey("institution_code")) {
            where += "and t0.institution_code in (" + (String) params.get("institution_code") + ") ";
        }
        if (params.containsKey("start")) {
            where += "and t0.start_date_time>='" + (String) params.get("start") + "' ";
        }
        if (params.containsKey("end")) {
            Date end = null;
            try {
                end = DateUtils.parseDate((String) params.get("end"), "yyyy-MM-dd");
            } catch (final ParseException e) {
            }
            if (null != end) {
                final Calendar c = Calendar.getInstance();
                c.setTime(end);
                c.add(Calendar.DAY_OF_MONTH, 1);
                where += "and t0.start_date_time<'" + DateFormatUtils.format(c.getTime(), "yyyy-MM-dd") + "' ";
            }
        }

        String sql = "SELECT "
                + statement
                + " FROM "
                + " neuiot_inspection_task_hn t0 "
                + "LEFT JOIN ( "
                + " SELECT "
                + "     t1.task_id, "
                + "     t1. STATUS, "
                + "     count(*) AS normal_item_count, "
                + "     t2.total_item_count "
                + " FROM "
                + "     neuiot_inspection_task_hn t0 "
                + " LEFT JOIN neuiot_inspection_task_item_value_hn t1 ON t0.id = t1.task_id "
                + " LEFT JOIN ( "
                + "     SELECT "
                + "         t1.task_id, "
                + "         count(*) AS total_item_count "
                + "     FROM "
                + "         neuiot_inspection_task_hn t0 "
                + "     LEFT JOIN neuiot_inspection_task_item_value_hn t1 ON t0.id = t1.task_id "
                + "     %s "
                + "     GROUP BY "
                + "         t1.task_id "
                + " ) t2 ON t0.id = t2.task_id "
                + " %s "
                + " AND t1. STATUS = 'normal' "
                + " GROUP BY "
                + "     t1.task_id, "
                + "     t1. STATUS, "
                + "     t2.total_item_count "
                + ") t1 ON t0.id = t1.task_id "
                + "LEFT JOIN ( "
                + "SELECT "
                + "task_id, "
                + "sum(total_count) as total_count, "
                + "sum(online_count) as online_count, "
                + "sum(offline_count) as offline_count "
                + "FROM "
                + "( "
                + " SELECT "
                + "     task_id, "
                + "     total_count, "
                + "     max( "
                + "         CASE ONLINE "
                + "         WHEN TRUE THEN "
                + "             total_count "
                + "         ELSE "
                + "             0 "
                + "         END "
                + "     ) 'online_count', "
                + "     max( "
                + "         CASE ONLINE "
                + "         WHEN FALSE THEN "
                + "             total_count "
                + "         ELSE "
                + "             0 "
                + "         END "
                + "     ) 'offline_count' "
                + " FROM "
                + "     ( "
                + "         SELECT "
                + "             t1.task_id, "
                + "             t1. ONLINE, "
                + "             count(t1. ONLINE) AS total_count "
                + "         FROM "
                + "             neuiot_inspection_task_hn t0 "
                + "         LEFT JOIN neuiot_inspection_task_asset_hn t1 ON t0.id = t1.task_id "
                + "         %s "
                + "         GROUP BY "
                + "             t1.task_id, "
                + "             t1. ONLINE "
                + "     ) t "
                + " GROUP BY "
                + "     task_id, "
                + "     total_count "
                + ") t "
                + "group by task_id "
                + ") t2 ON t0.id = t2.task_id "
                + "     %s ";

        sql = sql.replace("%s", where);

        return sql;
    }

    @Setter
    @Getter
    static class InspectionReportVO {

        @Excel(name = "巡检日期", orderNum = "0", width = 30)
        private Object start_date_time;

        @Excel(name = "服务厅局", orderNum = "1", width = 30)
        private Object institution_name;

        @Excel(name = "巡检人", orderNum = "2", width = 30)
        private Object user_name;

        @Excel(name = "巡检状态", orderNum = "3", width = 30)
        private Object status_name;

        @Excel(name = "完成时间", orderNum = "4", width = 30)
        private Object actual_date_time;

        @Excel(name = "巡检结果", orderNum = "5", width = 30)
        private Object result_text;

        @Excel(name = "总资产", orderNum = "6", width = 30)
        private Object asset_total_count;

        @Excel(name = "在线资产", orderNum = "7", width = 30)
        private Object online_count;

        @Excel(name = "下线资产", orderNum = "8", width = 30)
        private Object offline_count;

    }

}
